

--**************************************************
--*    wifi
--**************************************************
王一焯wlan密码：
wyc940102
--**************************************************
--*    睢宁crm数据接口调整2017-04-06
--**************************************************
#|1 任务：
1 更新人员信息表 ok
2 新增贷款信息 ok
3 资产分级字典 ok
#|2 数据开发规范：
1 新建表结构，务必注明字段中文名
2 数据库名、SCHEMA名、表名、表字段名，务必是大写
3 脚本务必有注释，标明脚本的作用
4 所有的数据库本身的保留字务必是大写
5 所有的自定义变量名务必是小写
6 数据库对象创建，务必指定表空间
#|3 查看root邮件，确定往常的调度情况
cat /var/spool/mail/db2inst1
#|4 人员信息表新增贷款信息
#|4.1 查看表 O_C03_P_GROUP_ALL 分区，每天etl的数据量大小
select * from syscat.datapartitions t where tabschema ='CRMSUINING' and tabname = 'O_C03_P_GROUP_ALL';
SELECT ETL_DT, COUNT(*) FROM CRMSUINING.O_C03_P_GROUP_ALL GROUP BY ETL_DT
#|4.2 查看表结构，取字段LOAN_BAL及其类型
CREATE TABLE CRMSUINING.tmptable like CRMSUINING.S_C03_P_GROUP_ALL;
db2look -d crm -e -z crmsuining -t O_C03_P_GROUP_ALL -o ~/outfile.sql
或者 IBM Data Studio 右击表名生成DDL语句
#|4.3 表 O_C03_P_GROUP_ALL 新增贷款字段，DDL脚本查看脚本文件 O_C03_P_GROUP_ALL.sql
SELECT * FROM CRMSUINING.S_C03_P_GROUP_ALL
SELECT * FROM CRMSUINING.O_C03_P_GROUP_ALL
SELECT * FROM CRMSUINING.O_C03_P_GROUP_ALL WHERE ETL_DT='2017-04-04';
#|4.5 表 L_C03_P_GROUP_ALL 新增贷款字段，DDL脚本查看脚本文件 L_C03_P_GROUP_ALL.sql
SELECT * FROM CRMSUINING.L_C03_P_GROUP_ALL;
SELECT COUNT(*) FROM CRMSUINING.L_C03_P_GROUP_ALL; --783732
SELECT * FROM CRMSUINING.L_C03_P_GROUP_ALL WHERE LOAN_BAL IS NOT NULL;
#|5 流水算法更新人员信息表, DDL脚本查看脚本文件 L_C03_P_GROUP_ALL.sql
SELECT COUNT(*) FROM CRMSUINING.L_C03_P_GROUP_ALL WHERE LOAN_BAL IS NOT NULL;
#|6 初始化数据
#|7 其它
DELETE FROM CRMSUINING.L_VIPLEVEL WHERE ID IN ('30', '31');
TRUNCATE table CRMSUINING.L_VIPLEVEL IMMEDIATE
@
SELECT * FROM CRMSUINING.L_VIPLEVEL
#|8 结果表保存为实体表
db2 connect to crm user db2inst1 using 123456
DROP TABLE CRMSUINING.T_TMP
@
CREATE TABLE CRMSUINING.T_TMP AS
(
  SELECT * FROM CRMSUINING.L_C03_P_GROUP_ALL
)DATA INITIALLY DEFERRED
REFRESH DEFERRED
@
REFRESH TABLE CRMSUINING.T_TMP
@
ALTER TABLE CRMSUINING.T_TMP DROP MATERIALIZED QUERY
@
SELECT * FROM CRMSUINING.T_TMP
--**************************************************
--*    验数
--**************************************************
SELECT ETL_DT, COUNT(*) FROM CRMSUINING.L_T03_CBOD_TDACNACN GROUP BY ETL_DT
SELECT ETL_DT, COUNT(*) FROM CRMSUINING.L_T03_CBOD_SAACNACN GROUP BY ETL_DT
SELECT ETL_DT, COUNT(*) FROM CRMSUINING.L_T03_CBOD_CRCRDCRD GROUP BY ETL_DT
SELECT ETL_DT, COUNT(*) FROM CRMSUINING.L_C03_P_GROUP_ALL GROUP BY ETL_DT
--**************************************************
--*    临时表
--**************************************************
WITH V_T1 AS
(
SELECT * FROM CRMSUINING.O_C03_P_GROUP_ALL
)
SELECT * FROM V_T1
--**************************************************
--*    TRUNCATE
--**************************************************
TRUNCATE table CRMSUINING.O_C03_P_GROUP_ALL IMMEDIATE
--**************************************************
--*    MERGE
--**************************************************
MERGE INTO A V_T1
USING B V_T2
ON (V_T1.a1=V_T2.b2)
WHEN MATCHED THEN
     UPDATE SET V_T1.a1 = V_T2.b1
WHEN NOT MATCHED THEN
     INSERT (V_T1.a1, V_T1.a2) VALUES (V_T2.b1,V_T2.b2)
--**************************************************
--*    表关联赋值
--**************************************************
UPDATE CRMSUINING.L_C03_P_GROUP_ALL AS V_T1
SET V_T1.SEASON_AVG=(
        SELECT V_T2.SEASON_AVG 
        FROM CRMSUINING.O_C03_P_GROUP_ALL AS V_T2
        WHERE V_T1.IDEN_NO=V_T2.IDEN_NO
fetch first 1 rows only
)
--**************************************************
--*    OUT 参数赋值
--**************************************************
CREATE OR REPLACE PROCEDURE CRMSUINING.SP_D_C03_P_GROUP_ALL(IN p_IDEN_NO VARCHAR(50), OUT p_STRING VARCHAR(1000))
LANGUAGE SQL
BEGIN
    SELECT
    'CUST_ID:'||CUST_ID||',CUST_NAME:'||CUST_NAME||',IDEN_NO:'||IDEN_NO||',IDEN_TYPE_CD:'||IDEN_TYPE_CD||',GENDER_CD:'||GENDER_CD||',BIRTH_DT:'||BIRTH_DT||',NATION_CD:'||NATION_CD||',HOME_PHONE:'||HOME_PHONE||',MOBILE_TELEPHONE:'||MOBILE_TELEPHONE||',EMAIL_ADDR:'||EMAIL_ADDR||',MARRIAGE_STATUS_CD:'||MARRIAGE_STATUS_CD||',HOME_ADDR:'||HOME_ADDR||',HOME_POST_CODE:'||HOME_POST_CODE||',CORP_NAME:'||CORP_NAME||',CORP_ADDR:'||CORP_ADDR||',CORP_TELEPHONE_NO:'||CORP_TELEPHONE_NO||',CUST_LEV:'||CUST_LEV
    INTO p_STRING
    FROM CRMSUINING.L_C03_P_GROUP_ALL
    WHERE IDEN_NO=p_IDEN_NO;
END
@
--CALL SP_D_C03_P_GROUP_ALL('320324198011050643')
@

--**************************************************
--*    返回结果集
--**************************************************
CREATE OR REPLACE PROCEDURE SP_D_C03_P_GROUP_ALL(p_IDEN_NO VARCHAR(50))
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE v_sql01 VARCHAR(5000);
    DECLARE v_stmt STATEMENT;
    DECLARE v_cursor CURSOR WITH RETURN TO CALLER FOR v_stmt;
    SET v_sql01 = '
    SELECT CUST_ID,CUST_NAME,IDEN_NO,IDEN_TYPE_CD,GENDER_CD,BIRTH_DT,NATION_CD,HOME_PHONE,MOBILE_TELEPHONE,EMAIL_ADDR,MARRIAGE_STATUS_CD,HOME_ADDR,HOME_POST_CODE,CORP_NAME,CORP_ADDR,CORP_TELEPHONE_NO,CUST_LEV
    FROM CRMSUINING.L_C03_P_GROUP_ALL
    WHERE IDEN_NO=?';
    PREPARE v_stmt FROM v_sql01;
    OPEN v_cursor USING p_IDEN_NO;
END
@
--CALL SP_D_C03_P_GROUP_ALL('320324198011050643')
@
--**************************************************
--*    死锁
--**************************************************
db2 "get snapshot for locks on sample"
db2 "force application(689)"
--**************************************************
--*    删除nickname
--**************************************************
CREATE INDEX "CRMSUINING"."L_C03_P_GROUP_ALL_IDEN_NO" ON "CRMSUINING"."L_C03_P_GROUP_ALL" ("IDEN_NO" ASC);
--**************************************************
--*    删除nickname
--**************************************************
DROP NICKNAME CRMSUINING.S_C03_CARD_NUM
--**************************************************
--*    新建数据库，表空间
--**************************************************
db2 create database local_db using codeset utf-8 territory cn collate using system
db2 connect to sample user db2inst1 using 123456
db2 "LIST TABLESPACES"
db2 "LIST TABLESPACES SHOW DETAIL"
db2 list db directory
db2 list node directory
db2 query client --查看连接的客户端
db2 create bufferpool bp32k all nodes size -1 pagesize 32k --新建缓冲池
db2 get db cfg|grep -i buff
rm -R /opt/tablespace101
touch /opt/tablespace101
chown db2inst1:db2iadm1 /opt/tablespace101
db2 "create regular tablespace  tablespace101 pagesize 32k managed by database using(file '/opt/tablespace101' 1g) bufferpool bp32k"
db2look -d sample -e -z db2inst1 -t employee -o ~/outfile.sql
CREATE TABLE "DB2INST1"."TEACHER"  (
    "NAME" VARCHAR(20)
    ,"AGE" INT
)   
IN "TABLESPACE101" ; 
--睢宁表空间规划
db2 create bufferpool bp32k all nodes size -1 pagesize 32k
rm /opt/disk02/tablespace101
touch /opt/disk02/tablespace101
chown db2inst1:db2iadm1 /opt/disk02/tablespace101
db2 "create regular tablespace  tablespace101 pagesize 32k managed by database using(file '/opt/disk02/tablespace101' 500g) bufferpool bp32k"
db2 "LIST TABLESPACES SHOW DETAIL"
db2 "DROP TABLESPACE  tablespace101"
db2 -td@ -f O_C03_P_GROUP_ALL.sql
db2 -td@ -f O_T03_CBOD_CRCRDCRD.sql
db2 -td@ -f L_C03_P_GROUP_ALL.sql
--**************************************************
--*    测试语句
--**************************************************
--查看分区
select * from syscat.datapartitions t where tabschema ='CRMSUINING' and tabname = 'O_C03_P_GROUP_ALL';
--删除分区
ALTER TABLE crmsuining.O_C03_P_GROUP_ALL DETACH PARTITION DP20170225 INTO PART0_TMP;
DROP TABLE PART0_TMP;
--测试数据是否重复
SELECT CR_PCRD_NO, COUNT(*) FROM CRMSUINING.O_T03_CBOD_CRCRDCRD GROUP BY CR_PCRD_NO HAVING COUNT(*) > 1
--查看每天同步数据量
SELECT ETL_DT, COUNT(*) FROM CRMSUINING.O_T03_CBOD_CRCRDCRD GROUP BY ETL_DT
--**************************************************
--*    查看源表大小，创建表结构。
--**************************************************
SELECT NPAGES, TBSPACE FROM SYSCAT.TABLES WHERE TABSCHEMA='PDATAVIEW' AND TABNAME='T03_CBOD_CRCRDCRD';
SELECT PAGESIZE/1024||'K' FROM SYSCAT.TABLESPACES WHERE TBSPACE='CRM';
--=216620*16/1024/1024=3G
SELECT * FROM CRM.T03_CBOD_CRCRDCRD
SELECT ETL_DT, COUNT(*) FROM PDATAVIEW.T03_CBOD_CRCRDCRD GROUP BY ETL_DT;
--每天都是全量递增200条数据。
db2 "create nickname crmsuining.S_T03_CBOD_CRCRDCRD for bdwsui.PDATAVIEW.T03_CBOD_CRCRDCRD"
CREATE TABLE crmsuining.t_tmp like crmsuining.S_T03_CBOD_CRCRDCRD; --获取字段类型信息
db2look -d crm -e -z crmsuining -t t_tmp -o ~/outfile.sql
--**************************************************
--*    银行卡号
--**************************************************
SELECT * FROM crmsuining.S_C03_P_GROUP_ALL WHERE CUST_ID='105514003714';
--**************************************************
--*    connect type 1 is in use 问题
--**************************************************
db2 connect reset
--**************************************************
--*    事务日志已满问题 db2 ? 57011
--**************************************************
ALTER TABLE crmsuining.O_C03_P_GROUP_ALL ACTIVATE NOT LOGGED INITIALLY;
db2 update command options using c off
db2 list command options
db2set db2options=+c --关闭自动提交选项
db2set db2options=
lcd D:\03_TREE\01_git\xuechengcheng1104.github.io\trunk\script
cd ~/
put 20_input.sh
db2 connect to sample user db2inst1 using 123456
db2 -td@ -f ~/20_input.sh
db2 get db config for sample | grep -i logfilsiz --忽略大小写
db2 update db cfg for sample using LOGFILSIZ 20000   ---更改 日志文件大小
db2 update db cfg for sample using LOGPRIMARY 15     ---更改 主日志文件的数目
db2 update db cfg for sample using LOGSECOND  8      ---更改 辅助日志文件的数目
db2 force applications all
db2 list application
db2stop
db2start
db2>connect to dbName user userName using Passowrd
db2>update command options using c off
db2>ALTER TABLE tableName activate NOT LOGGED initially
db2>delete from tableName where a<100000
db2>commit
--**************************************************
--*    复制表结构，并导出表结构，增删分区
--**************************************************
CREATE TABLE crmsuining.O_C03_P_GROUP_ALL like crmsuining.S_C03_P_GROUP_ALL; --获取字段类型信息
db2look -d crm -e -z crmsuining -t O_C03_P_GROUP_ALL -o ~/outfile.sql
select * from syscat.datapartitions t where tabschema ='CRMSUINING' and tabname = 'O_C03_P_GROUP_ALL' --查看分区
ALTER TABLE crmsuining.O_C03_P_GROUP_ALL ADD PARTITION dp20170224 STARTING FROM '2017-02-24' ENDING AT '2017-02-24' INCLUSIVE; --新增分区
ALTER TABLE crmsuining.O_C03_P_GROUP_ALL DETACH PARTITION DP20170225 INTO PART0_TMP; --删除分区
DROP TABLE PART0_TMP;
SELECT ETL_DT, COUNT(*) FROM crmsuining.O_C03_P_GROUP_ALL GROUP BY ETL_DT;

--**************************************************
--*    定时任务
--**************************************************
crontab -e
*/1 * * * * echo 'hello' >> ~/hello.q --每隔一分钟
30 21 * * * /usr/local/etc/rc.d/lighttpd restart --每天21:30分
crontab -l
crontab -u db2inst1 -l --root账号查看账号db2inst1的任务
crontab -u fred -r --root账号删除账号fred的任务
vi /etc/crontab
cat /tmp/crontab.aTz9Qk
0 2 * * * /home/db2inst1/file/JobSchedule.sh
cat /home/db2inst1/file/JobSchedule.sh
vi /home/db2inst1/file/JobSchedule.sh
sh /home/db2inst1/file/JobSchedule.sh
cat /var/log/cron | grep db2 --查看日志
less /home/db2inst1/sqllib/db2dump/db2diag.log
lcd D:\03_TREE\01_git\ETL.github.io\trunk\BankSuiNingNongShang\tool
cd ~
put JobSchedule.sh
lcd D:\03_TREE\01_git\ETL.github.io\trunk\BankSuiNingNongShang\script\O_level\
cd ~
put O_C03_P_GROUP_ALL.sql
put O_T03_CBOD_CRCRDCRD.sql
/home/db2inst1/sqllib/bin/db2 connect to crm user db2inst1 using 123456
db2 -td@ -f O_C03_P_GROUP_ALL.sql
db2 -td@ -f O_T03_CBOD_CRCRDCRD.sql
/home/db2inst1/sqllib/bin/db2 connect to crm user db2inst1 using 123456
44 11 * * * /home/db2inst1/sqllib/bin/db2 connect to crm user db2inst1 using 123456
cat /var/spool/mail/db2inst1
cat /var/spool/cron/db2inst1
cat /etc/crontab
--**************************************************
--*    常用命令
--**************************************************
Db2 "LIST TABLESPACES"
Db2 "LIST TABLESPACES SHOW DETAIL"
db2 describe table ilya.employee05 --查看表结构
db2 ? 42601 --查看错误代码解释
alter table ilya.t_date add column v_time time; --增加列
create or replace table ilya.employee05 like ilya.employee; --建表
select * from syscat.datapartitions t where tabschema =模式名 and tabname = 表名 --查看分区
--**************************************************
--*    获取变量，执行SQL语句
--**************************************************
CREATE OR REPLACE PROCEDURE sp_example()
LANGUAGE SQL
BEGIN
declare v_date DATE;
declare v_time time;
SELECT CURRENT_DATE, CURRENT_TIME into v_date, v_time FROM SYSIBM.SYSDUMMY1;
INSERT INTO t_date VALUES (V_DATE, v_time);
END
@
call sp_example()
@
--**************************************************
--*    DB2分区表
--**************************************************
DROP TABLE ilya.O_C03_P_GROUP_ALL
@
CREATE TABLE ilya.O_C03_P_GROUP_ALL
(
	CUST_NAME VARCHAR(252)
	,IDEN_TYPE_CD VARCHAR(252)
	,IDEN_NO VARCHAR(252)
	,GENDER_CD VARCHAR(252)
	,BIRTH_DT DATE
	,CORP_TELEPHONE_NO VARCHAR(252)
	,HOME_PHONE VARCHAR(252)
	,EMAIL_ADDR VARCHAR(252)
	,HOME_ADDR VARCHAR(252)
	,HOME_POST_CODE VARCHAR(252)
	,MOBILE_TELEPHONE VARCHAR(252)
	,CORP_ADDR VARCHAR(252)
	,MARRIAGE_STATUS_CD VARCHAR(252)
	,CORP_NAME VARCHAR(252)
	,NATION_CD VARCHAR(252)
	,CUST_LEV VARCHAR(252)
	,ETL_DT DATE
)PARTITION BY RANGE(ETL_DT)
(STARTING ('1/1/1999') ENDING ('12/31/2099') EVERY 1 MONTH)
@
http://blog.chinaunix.net/uid-196042-id-2103401.html
--**************************************************
--*    DB2参考书籍
--**************************************************
DB2SQLProceduralLangs-db2plse1011 --存储过程开发：
DB2Partitioning-db2pce1011 --分区表创建
--**************************************************
--*    返回结果集
--**************************************************
CREATE OR REPLACE PROCEDURE sp_example(lastname char(20))
dynamic result sets 1
BEGIN
  declare str varchar(100);
  declare stmt statement;
  declare c1 cursor with return to caller for stmt;
  set str = 'select lastname, hiredate from employee where lastname=?';
  prepare stmt from str;
  open c1 using lastname;
END
@
call sp_example('HAAS')
@
--**************************************************
--*    DB2创建数据库
--**************************************************
db2 list database directory
db2 create database crm using codeset utf-8 territory cn collate using system
db2 connect reset
db2 disconnect current
db2 quit
db2 terminate
--**************************************************
--*    导出建表语句
--**************************************************
db2look -d crm -e -z crmsuining -t S_C03_P_GROUP_ALL -o outfile.sql
--**************************************************
--*    查看linux内存和磁盘
--**************************************************
free -h
df -h
--**************************************************
--*    linux装db2端口50000连不上
--**************************************************
db2set -all
db2set DB2COMM=TCPIP
vi /etc/services
db2c_db2inst1 50000/tcp
db2c_db2inst1 50000/udp
db2 get dbm cfg | grep "SVCENAME" --查看db2管理器配置
db2 get db cfg for crm --查看数据库配置
cd ~
db2 update database manager configuration using svcename db2c_db2inst1
db2set DB2COMM=tcpip 
db2set DB2CODEPAGE=1386 
db2set DB2AUTOSTART=YES
db2stop
db2start
db2set -all
firewall-cmd --add-port=50000/tcp
netstat -an | grep 50000
su db2inst1
--**************************************************
--*    db2b linux 安装
--**************************************************
lcd F:\00\01_Software\database\db2v9.7
cd /opt
put DB2.tar.gz
cd /opt
tar -zvxf DB2.tar.gz
cd ese
./db2_install
输入： no, ESE, 
报错（暂且不管）：WARNING: A minor error occurred while installing "DB2 Enterprise Server Edition" on this computer. Some features may not function correctly.
less /tmp/db2_install.log.2897
less /tmp/prereqSAM.log.39658
mount -o loop /opt/rhel-server-6.5-x86_64-dvd.iso /opt/iso_rhel6/
groupadd -g 2000 db2iadm1
groupadd -g 2001 db2fadm1
useradd -m -g db2iadm1 -d /home/db2inst1 db2inst1
useradd -m -g db2fadm1 -d /home/db2fenc1 db2fenc1
passwd db2inst1
passwd db2fenc1
cd /opt/ibm/db2/V9.7/instance
chmod -R 775 *
#创建实例，数据库
/opt/ibm/db2/V9.7/instance/db2icrt -p 50000 -u db2fenc1 db2inst1
su - db2inst1
/opt/ibm/db2/V9.7/bin/db2 create database crm using codeset utf-8 territory cn collate using system
/opt/ibm/db2/V9.7/bin/db2 connect to CRM user db2inst1 using 123456
#查看哪些实例
/opt/ibm/db2/V9.7/instance/db2ilist
/opt/ibm/db2/V9.7/bin/db2 get instance #查看当前所在实例
/opt/ibm/db2/V9.7/bin/db2 list db directory #查看哪些数据库
/opt/ibm/db2/V9.7/bin/db2 'SELECT current date FROM sysibm.sysdummy1'
--**************************************************
--*    db2b linux 安装
--**************************************************
http://www.codeceo.com/article/linux-db2-setup.html
lcd C:\Users\ilya\Downloads
cd /opt
put db2_v101_linuxx64_expc.tar.gz
tar -zxvf db2_v101_linuxx64_expc.tar.gz
cd expc/
./db2_install
groupadd -g 2000 db2iadm1
groupadd -g 2001 db2fadm1
useradd -m -g db2iadm1 -d /home/db2inst1 db2inst1
useradd -m -g db2fadm1 -d /home/db2fenc1 db2fenc1
passwd db2inst1
passwd db2fenc1
cd /opt/ibm/db2/V10.1/instance
chmod -R 775 *
./db2icrt -p 50000 -u db2fenc1 db2inst1
db2sampl
/opt/expc/db2prereqcheck
lcd E:\00\01_Software
cd /opt
put rhel-server-7.0-x86_64-dvd.iso
mkdir /iso_rhel
mount /opt/rhel-server-7.0-x86_64-dvd.iso /iso_rhel
vi /etc/yum.repos.d/local.repo
[base]
name=local
baseurl=file:///iso_rhel
gpgcheck=0
enabled=1
yum makecache
yum install vim
su db2inst1
db2start
yum install libaio.so.1
yum install libaio
cd /opt/ibm/db2/V10.1/instance
./db2ilist
./db2idrop  db2inst1
./db2icrt -p 50000 -u db2fenc1 db2inst1
groupadd -g 2002 db2ilya
useradd -m -g db2ilya -d /home/db2ilya db2ilya
db2 grant connect on sample to user db2ilya
db2 connect to sample user db2inst1 using 123456
--**************************************************
--*    db2以本地用户（windows）登录
--**************************************************
search windows application 'cmd'
right click, click 'run as adminstrator'
input db2cmd, occur command window
execut command 'db2sampl -name dbtest'
--**************************************************
--*    DB2联邦数据库创建
--**************************************************
https://www.ibm.com/developerworks/cn/data/library/techarticles/0203haas/0203haas.html
db2 catalog tcpip node OLIVER remote localhost server 50000
db2 catalog tcpip node suining remote 32.107.160.53 server 50000
db2 catalog db BDW at node suining
db2 connect to BDW user db2read using db2read
db2 uncatalog node OLIVER
db2 uncatalog node suining
db2 uncatalog db BDW
db2 terminate
db2 get dbm cfg | grep "FEDERATED"
db2 update dbm cfg using FEDERATED YES
db2 force application all
db2stop
db2start
db2 list db directory
db2 connect to dbtest
db2 connect to crm
db2 CREATE WRAPPER DRDA
db2sampl -name dbremote
db2level
db2 "create server CONN_BIR type DB2/UDB version 10.1 wrapper "DRDA" authorization \"db2admin\" password \"123456\" options (NODE 'OLIVER',dbname 'dbremote')"
db2 "create server bdwsui type DB2/UDB version 9.7 wrapper "DRDA" authorization \"db2read\" password \"db2read\" options (NODE 'suining',dbname 'BDW')"
db2 list db directory
db2 create user mapping for "db2admin" server CONN_BIR options (remote_authid 'db2admin', remote_password '123456')
create user mapping for "db2inst1" server bdwsui options (remote_authid 'db2read', remote_password 'db2read') --在开发窗口执行
Create User Mapping for db2inst1 server "bdwsui" options (remote_authid 'db2read', remote_password 'db2read')
db2 list node directory
create schema crmsuining
create nickname ilya.student_phone_alias for CONN_BIR.db2admin.student_phone
create nickname crmsuining.S_C03_P_GROUP_ALL for bdwsui.CRM.C03_P_GROUP_ALL
create nickname ilya.student_phone_alias for CONN_BIR.db2admin.student_phone
SELECT * FROM ilya.student_phone_alias
SELECT * FROM crmsuining.S_C03_P_GROUP_ALL
--创建映射表
db2 "create nickname crmsuining.S_C03_CARD_NUM for bdwsui.CRM.C03_CARD_NUM"
--**************************************************
--*    DB2链接工具
--**************************************************
download dbvis_windows-x64_9_5_6.exe
install jdk, configure JAVA_HOME
db2 connect to SAMPLE user db2admin using 123456
db2sampl -name dbtest
--**************************************************
--*    DB2 ERROR Code
--**************************************************
db2 ? 42622
--**************************************************
--*  The instance may have been shutdown as a result.
--**************************************************
报错：The instance may have been shutdown as a result.
方法：定时调度脚本任务要root用户执行